const db=require('../db/index')
const moment=require('moment')

exports.purall=(req,res)=>{
    const sql='select * from purchase_view'
    db.query(sql,(err,results)=>{
        if(err) return res.cc(err)

        res.json(results)
    })
}

exports.purfind=(req,res)=>{
    var info=req.body
    var type=info.selecttype
    var sql=` select * from purchase_view `
    if(info.inputtext.length!=0){
        var name=info.inputtext
        sql+=` where `
        if(type=='med_name')
        {
            sql+=` med_name like '%${name}%' `
        }else if(type=='cust_name')
        {
            sql+=` cust_name like '%${name}%' `
        }else if(type=='doc_name')
        {
            sql+=` doc_name like '%${name}%' `
        }else if(type=='state'){
            sql+=` state = '${name}' `
        }
    }
    
    if(info.start_date.length!=0||info.end_date.length!=0)
    {
           
    var start_date=moment(info.start_date).format('YYYY-MM-DD HH:mm:ss')
    var end_date=moment(info.end_date).format('YYYY-MM-DD HH:mm:ss')
        if(info.inputtext.length!=0){
            sql+=` and `
        }else if(info.inputtext.length==0)
        {
            sql+=` where `
        }
        if(info.start_date.length!=0&&info.end_date.length!=0){
            sql+=` pur_or_time >= '${start_date}' and pur_or_time <= '${end_date}' `
        }else if(info.start_date.length!=0){
            sql+=` pur_or_time >= '${start_date}' `
        }else if(info.end_date.length!=0){
            sql+=` pur_or_time <= '${end_date}' `
        }
    }
    console.log(sql)
    db.query(sql,(err,results)=>{
        if(err) return res.cc(err)
        res.json(results)
    })
}

exports.purdeleteid=(req,res)=>{
    const info=req.body
    const sql='delete from purchase_order where pur_or_id=?'
    db.query(sql,info.id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.affectedRows!=1) return res.cc('failed to delete')
        res.cc('delete successfully',0)
    })
}


exports.purupdate=(req,res)=>{
    const info=req.body
    const sql='select * from purchase_order where pur_or_id=?'
    db.query(sql,info.id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.length!=1) return res.cc('purchase order not exist')
        const sql='select * from customer where cust_id=?'
        db.query(sql,info.cust_id,(err,results)=>{
            if(err) return res.cc(err)
            if(results.length!=1) return res.cc('customer not exist')
            const sql='select * from medicine where med_id=? '
            db.query(sql,info.med_id,(err,results)=>{
                if(err) return res.cc(err)
                if(results.length!=1) return res.cc('medicine not exist')
                if(results[0].med_num-info.num<0) return res.cc('medicine inventory shortage')
                const sql='select * from doctor where doc_id=?'
                db.query(sql,info.doc_id,(err,results)=>{
                    if(err) return res.cc(err)
                    if(results.length!=1) return res.cc('doctor not exist')
                    const sql='update purchase_order set ? where pur_or_id=?'
                    db.query(sql,[{doc_id:info.doc_id,cust_id:info.cust_id,state:info.state,med_id:info.med_id,med_pur_num:info.num,med_pur_price:info.price,comments:info.comments},info.id],(err,results)=>{
                        if(err) res.cc(err)
                        if(results.affectedRows!=1) res.cc('failed to update')
                        res.cc('update successfully',0)
                    })
                })
            })
        })
    })
}

exports.purinsert=(req,res)=>{
    const info=req.body
    const time =moment().format('YYYY-MM-DD HH:mm:ss');
    const sql='select * from customer where cust_id=?'
    db.query(sql,info.cust_id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.length!=1) return res.cc('customer not exist')
        const sql='select * from medicine where med_id=? '
        db.query(sql,info.med_id,(err,results)=>{
            if(err) return res.cc(err)
            if(results.length!=1) return res.cc('medicine not exist')
            if(results[0].med_num-info.num<0) return res.cc('medicine inventory shortage')
            const sql='select * from doctor where doc_id=?'
            db.query(sql,info.doc_id,(err,results)=>{
                if(err) return res.cc(err)
                if(results.length!=1) return res.cc('doctor not exist')
                const sql='insert into purchase_order set ?'
                db.query(sql,{doc_id:info.doc_id,cust_id:info.cust_id,pur_or_time:time,state:info.state,med_id:info.med_id,med_pur_num:info.num,med_pur_price:info.price,comments:info.comments},(err,results)=>{
                    if(err) res.cc(err)
                    if(results.affectedRows!=1) res.cc('failed to insert')
                    res.cc('insert successfully',0)
                })
            })
        })
    })
}